package com.fzhucc.modules.pms.repository;

import com.fzhucc.modules.pms.domain.Project;
import com.fzhucc.modules.sciPersonnelInfo.domain.SciPersonnelInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.validation.constraints.NotNull;
import java.util.Date;
import java.util.List;

/**
 * @author Woolsen
 */
public interface ProjectRepository extends JpaRepository<Project, String>, JpaSpecificationExecutor<Project> {

    @Query(nativeQuery = true, value = "SELECT p.already_edit FROM pms_project p WHERE p.id=:id")
    Boolean findAlreadyEditById(String id);

    /**
     * 通过专家用户来查找专家需评分的项目
     *
     * @param userId 专业用户ID
     * @return 专家用户可以评分的项目列表
     */
    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p INNER JOIN (SELECT project_id FROM pms_project_score ps INNER JOIN (SELECT id FROM tt_expert WHERE user_id=:userId) e ON ps.expect_id=e.id) ps ON p.id=ps.project_id and (p.del_flag=0 or p.del_flag is null)")
    Page<Project> findAllByExpectUser(@NotNull Long userId, Pageable pageable);

    /**
     * 通过用户来查找技术经理人的项目
     *
     * @param userId 用户ID
     * @return 技术经理人能操作的项目列表
     */
    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p INNER JOIN (SELECT project_id FROM tt_technical_project_log ps INNER JOIN (SELECT id FROM tt_technical WHERE user_id=:userId) e ON ps.technical_id=e.id) ps ON p.id=ps.project_id and (p.del_flag=0 or p.del_flag is null)")
    Page<Project> findAllByTechnicalUser(@NotNull Long userId, Pageable pageable);

    /**
     * 通过用户来查找院士之家的项目
     *
     * @param userId 用户ID
     * @return 院士之家能操作的项目列表
     */
    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p INNER JOIN (SELECT project_id FROM tt_academician_project_log ps INNER JOIN (SELECT id FROM tt_academician WHERE user_id=:userId) e ON ps.academician_id=e.id) ps ON p.id=ps.project_id and (p.del_flag=0 or p.del_flag is null)")
    Page<Project> findAllByAcademicianUser(@NotNull Long userId, Pageable pageable);

    /**
     * 通过用户来查找产业资本的项目
     *
     * @param userId 用户ID
     * @return 产业资本能操作的项目列表
     */
    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p INNER JOIN (SELECT project_id FROM tt_capital_project_log ps INNER JOIN (SELECT id FROM tt_capital WHERE user_id=:userId) e ON ps.capital_id=e.id) ps ON p.id=ps.project_id and (p.del_flag=0 or p.del_flag is null)")
    Page<Project> findAllByCapitalUser(@NotNull Long userId, Pageable pageable);

    /**
     * 通过用户来查找产业资本的项目
     *
     * @param userId 用户ID
     * @return 产业资本能操作的项目列表
     */
    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p INNER JOIN (SELECT project_id FROM tt_company_project_log ps INNER JOIN (SELECT id FROM tt_company WHERE user_id=:userId) e ON ps.company_id=e.id) ps ON p.id=ps.project_id and (p.del_flag=0 or p.del_flag is null)")
    Page<Project> findAllByCompanyUser(@NotNull Long userId, Pageable pageable);

    /**
     * 通过用户来查找产业龙头的项目
     *
     * @param userId 用户ID
     * @return 产业龙头能操作的项目列表
     */
    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p INNER JOIN (SELECT project_id FROM tt_leader_project_log ps INNER JOIN (SELECT id FROM tt_leader WHERE user_id=:userId) e ON ps.leader_id=e.id) ps ON p.id=ps.project_id and (p.del_flag=0 or p.del_flag is null)")
    Page<Project> findAllByLeaderUser(@NotNull Long userId, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT MAX(level) FROM pms_project WHERE user_id=:userId and (del_flag=0 or del_flag is null)")
    Integer findMaxLevelByUserId(@NotNull Long userId);

    @Query(nativeQuery = true, value = "SELECT * FROM pms_project WHERE user_id=:userId and (del_flag=0 or del_flag is null)")
    List<Project> findByUserId(@NotNull Long userId);

    @Query(nativeQuery = true, value = "SELECT * FROM pms_project WHERE project_name=:projectName and phone=:phone and (del_flag=0 or del_flag is null)")
    List<Project> findByProjectNameWithPhone(@NotNull String projectName, String phone);

    @Query(nativeQuery = true, value = "SELECT * FROM pms_project WHERE project_name=:projectName and source=:source")
    List<Project> findByProjectNameWithSource(@NotNull String projectName, String source);

    @Query(nativeQuery = true, value = "SELECT * FROM pms_project WHERE project_name=:projectName")
    List<Project> findByProjectName(@NotNull String projectName);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where level=:level and (del_flag=0 or del_flag is null)")
    int findByType(int level);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where (del_flag=0 or del_flag is null)")
    int findAllNormal();

    @Query(nativeQuery = true, value = "select count(*) from pms_project as p where p.source like %?1% and (p.del_flag=0 or p.del_flag is null)")
    int findLikeSource(String source);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where source = ?1 and (del_flag=0 or del_flag is null) ")
    int findBySource(String source);

    @Query(nativeQuery = true, value = "select count(*) from pms_project as p where p.race_domain=:racedomain and (p.del_flag=0 or p.del_flag is null) and p.level=:level")
    int findbyLevelAndRacedomain(String racedomain, int level);

    @Query(nativeQuery = true, value = "select count(*) from pms_project as p where p.race_domain is null and (p.del_flag=0 or p.del_flag is null) and p.level=:level")
    int findbyLevelAndRacedomainIsnull(int level);

    /**
     * 第一届的特殊项目
     */
    @Query(nativeQuery = true, value = "select count(*) from pms_project where race_domain is NOT null and race_domain!='人工智能' and race_domain!='光电制造'and race_domain!='生物工程'and race_domain!='化纤棉纺' and race_domain!='汽车制造' and race_domain!='其他'and race_domain!='智慧城市'and race_domain!='信息软件' and race_domain!='节能减排' and race_domain!='乡村振兴' and race_domain!='文化旅游' and race_domain!='新材料' and race_domain!='新技术' and race_domain!='新能源' and (del_flag=0 or del_flag is null) and level=:level")
    int findbyLevelAndRacedomainNoContains(int level);

    @Query(nativeQuery = true, value = "select count(*) from pms_project as p where p.level=:level and (p.del_flag=0 or p.del_flag is null)")
    int queryByLevel(int level);

    @Query(nativeQuery = true, value = "select count(*) from pms_project as p where p.level=:level and p.org_area=:orgArea and (p.del_flag=0 or p.del_flag is null)")
    int queryByLevelOrgAreaO(int level, String orgArea);

    @Query(nativeQuery = true, value = "select count(*) from pms_project as p where p.level=:level and (p.del_flag=0 or p.del_flag is null)")
    int queryByLevelOrgArea(int level);


    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.phone=:phone and p.del_flag!=1 and p.org_area=:orgArea ) or (p.user_id=:userId and p.del_flag!=1 and p.org_area=:orgArea  )")
    Page<Project> findMeAllO(@NotNull Long userId, String phone, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.phone=:phone and p.del_flag!=1 and p.org_area =:orgArea and p.source not like :text ) or (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source not like :text )")
    Page<Project> findMeAllKcO(@NotNull Long userId, String phone, String text, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.phone=:phone and p.del_flag!=1 and p.org_area =:orgArea and p.source like :text ) or (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source like :text )")
    Page<Project> findMeAllWxO(@NotNull Long userId, String phone, String text, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea)")
    Page<Project> findMeWithOutPhoneAllO(@NotNull Long userId, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source not like :text )")
    Page<Project> findMeWithOutPhoneAllKcO(@NotNull Long userId, String text, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source like :text )")
    Page<Project> findMeWithOutPhoneAllWxO(@NotNull Long userId, String text, String orgArea, Pageable pageable);


    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE ((p.phone=:phone and p.del_flag!=1 and p.org_area=:orgArea ) or (p.user_id=:userId and p.del_flag!=1 and p.org_area=:orgArea )) and p.project_name like :projectName")
    Page<Project> findMeAllLikeNameO(@NotNull Long userId, String phone, String orgArea, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE ((p.phone=:phone and p.del_flag!=1 and p.org_area =:orgArea and p.source not like :text ) or (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source not like :text )) and p.project_name like :projectName ")
    Page<Project> findMeAllKcLikeNameO(@NotNull Long userId, String phone, String text, String projectName, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE ((p.phone=:phone and p.del_flag!=1 and p.org_area =:orgArea and p.source like :text ) or (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source like :text )) and p.project_name like :projectName")
    Page<Project> findMeAllWxLikeNameO(@NotNull Long userId, String phone, String text, String projectName, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.project_name like :projectName )")
    Page<Project> findMeWithOutPhoneAllLikeNameO(@NotNull Long userId, String projectName, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source not like :text and p.project_name like :projectName  )")
    Page<Project> findMeWithOutPhoneAllKcLikeNameO(@NotNull Long userId, String text, String projectName, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.org_area =:orgArea and p.source like :text  and p.project_name like :projectName)")
    Page<Project> findMeWithOutPhoneAllWxLikeNameO(@NotNull Long userId, String text, String projectName, String orgArea, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT * FROM pms_project WHERE del_flag=0")
    List<Project> findAllByNormalStatus();

    /*@Query(nativeQuery = true, value = "SELECT p.id as id,p.project_name as projectName,p.create_time as createTime,p.project_img_url as projectImgUrl,p.item_category as itemCategory FROM pms_project p WHERE p.del_flag=0")
    List<Project> findAllByNormalStatusWithPart();*/

    /*@Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE p.del_flag=0")
    List<Project> findAllByNormalStatusWithPart();*/


    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.phone=:phone and p.del_flag!=1  ) or (p.user_id=:userId and p.del_flag!=1  )")
    Page<Project> findMeAll(@NotNull Long userId, String phone, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.phone=:phone and p.del_flag!=1  and p.source not like :text ) or (p.user_id=:userId and p.del_flag!=1 and p.source not like :text )")
    Page<Project> findMeAllKc(@NotNull Long userId, String phone, String text, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.phone=:phone and p.del_flag!=1  and p.source like :text ) or (p.user_id=:userId and p.del_flag!=1  and p.source like :text )")
    Page<Project> findMeAllWx(@NotNull Long userId, String phone, String text, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 )")
    Page<Project> findMeWithOutPhoneAll(@NotNull Long userId, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1 and p.source not like :text )")
    Page<Project> findMeWithOutPhoneAllKc(@NotNull Long userId, String text, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1  and p.source like :text )")
    Page<Project> findMeWithOutPhoneAllWx(@NotNull Long userId, String text, Pageable pageable);


    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE ((p.phone=:phone and p.del_flag!=1  ) or (p.user_id=:userId and p.del_flag!=1 )) and p.project_name like :projectName")
    Page<Project> findMeAllLikeName(@NotNull Long userId, String phone, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE ((p.phone=:phone and p.del_flag!=1  and p.source not like :text ) or (p.user_id=:userId and p.del_flag!=1  and p.source not like :text )) and p.project_name like :projectName ")
    Page<Project> findMeAllKcLikeName(@NotNull Long userId, String phone, String text, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE ((p.phone=:phone and p.del_flag!=1  and p.source like :text ) or (p.user_id=:userId and p.del_flag!=1  and p.source like :text )) and p.project_name like :projectName")
    Page<Project> findMeAllWxLikeName(@NotNull Long userId, String phone, String text, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1  and p.project_name like :projectName )")
    Page<Project> findMeWithOutPhoneAllLikeName(@NotNull Long userId, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1  and p.source not like :text and p.project_name like :projectName  )")
    Page<Project> findMeWithOutPhoneAllKcLikeName(@NotNull Long userId, String text, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT p.* FROM pms_project p WHERE (p.user_id=:userId and p.del_flag!=1  and p.source like :text  and p.project_name like :projectName)")
    Page<Project> findMeWithOutPhoneAllWxLikeName(@NotNull Long userId, String text, String projectName, Pageable pageable);

    @Query(nativeQuery = true, value = "select * from pms_project where del_flag=0 and org_area like :area ")
    List<Project> findNormalByLikeArea(String area);

    @Query(nativeQuery = true, value = "select * from pms_project where del_flag=0 ")
    List<Project> findNormalByLikeAreaFJS();


    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0 and org_area like :area  and create_time<=NOW() and create_time>=:atime")
    int findNormalByLikeAreaWithOutIsShowCSeven(String area, Date atime);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0 and org_area like :area  and delete_time<=NOW() and delete_time>=:dtime")
    int findNormalByLikeAreaWithOutIsShowDSeven(String area, Date dtime);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0   and create_time<=NOW() and create_time>=:atime")
    int findNormalByLikeAreaWithOutIsShowCSevenFJS(Date atime);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0   and delete_time<=NOW() and delete_time>=:dtime")
    int findNormalByLikeAreaWithOutIsShowDSevenFJS(Date dtime);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0 and org_area like :area ")
    int findCountNormalByLikeArea(String area);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0  and org_area like:area and create_time<=:eTime")
    int findCountNormalByLikeAreaWithTime(String area, Date eTime);


    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0 and org_area like :area  and create_time<=:btime and create_time>=:atime")
    int findNormalByLikeAreaWithOutIsShowCSevenCreate(String area, Date atime, Date btime);

    @Query(nativeQuery = true, value = "select count(*) from pms_project where del_flag=0 and org_area like :area  and delete_time<=:btime and delete_time>=:atime")
    int findNormalByLikeAreaWithOutIsShowDSevenDel(String area, Date atime, Date btime);
}